﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GenUtil.Dto;
using System.Data.Common;
using GenUtil.Util;


namespace DataLayer.Dao.Sql
{
    class LoginDaoSql : BasicDaoSql, LoginDao
    {
        private static Logger logger = new Logger(typeof(CustomerDaoSql));

        private static string SQL_INSERT_LOGIN = @" INSERT INTU " + SCHEMA + @" .LOGIN(USER_ID,PASSWORD) VALUES (@USER_ID,@PASSWORD)";

        public void InsertLoginDto(LoginDto loginDto)
        {
            logger.debug("Entering LoginDaoSql.InsertLoginDto()");
            DbConnection con = null;
            DbCommand com = null;
            try
            {
                con = getDatabaseConnection();
                com = CreateDBCommand(con, SQL_INSERT_LOGIN);
                setStatement(null, com, loginDto);
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                logger.fatal("Exception in LoginDaoSql.InsertLoginDto():" + e.Message, e);
                throw new DaoException("Exception in LoginDaoSql.InsertLoginDto(): " + e.Message, e);
            }
            finally
            {
                if (com != null) com.Cancel();
                if (con != null) con.Close();
            }
            logger.debug("Exiting LoginDaoSql.InsertCustomerDto()");
        }

        private void setStatement(String methodName, DbCommand com, LoginDto loginDto)
        {
            DbParameter[] chkPara = CreateDBParameter(com, 2);

            if (methodName == null)
            {
                PopulateDBParameter(chkPara[0], "USER_ID", loginDto.UserId);
                PopulateDBParameter(chkPara[1], "PASSWORD", loginDto.Password);

                com.Parameters.AddRange(chkPara);

            }
        }

        private static string SQL_GET_LOGIN = @"SELECT * FROM LOGIN WHERE USER_ID=@USER_ID";

        public LoginDto GetLoginDto(int userId)
        {
            logger.debug("Entering LoginDaoSql.getLoginDto()");

            LoginDto loginDto = null;

            DbConnection con = null;
            DbCommand pstmt = null;
            DbDataReader rs = null;

            Dictionary<string, object> stringofpara = new Dictionary<string, object>();
            stringofpara.Add("userId", userId);

            try
            {
                con = getDatabaseConnection();
                pstmt = CreateDBCommand(con, SQL_GET_LOGIN);
                DbParameter[] para = CreateDBParameter(pstmt, 1);
                PopulateDBParameter(para[0], "USER_ID", userId);
                pstmt.Parameters.AddRange(para);
                rs = pstmt.ExecuteReader();

                if (rs.Read())
                {
                    loginDto = rsToDto(rs);
                }
            }
            catch (Exception e)
            {
                logger.fatal("Exception occured in LoginDaoSql.getLoginDto():" + e.Message, e);
                throw new Exception("Exception : LoginDaoSql.getLoginDto() : " + e.Message, e);
            }
            finally
            {
                if (rs != null) rs.Close();
                if (pstmt != null) pstmt.Dispose();
                if (con != null) con.Close();
            }
            logger.debug("exiting LoginDaoSql.getLoginDto()");
            return loginDto;
        }

        private LoginDto rsToDto(DbDataReader rs)
        {
            LoginDto odLoginDto = null;

            if (rs != null)
            {
                odLoginDto = new LoginDto();

                odLoginDto.setUserId(getStringValue(rs, "USER_ID"));
                odLoginDto.setPassword(getStringValue(rs, "PASSWORD"));

            }
            return odLoginDto;
        }

        private static string SQL_SELECT_LOGIN = @"SELECT * FROM LOGIN ";

        public List<LoginDto> getLoginDto()
        {
            logger.debug("Entering LoginDaoSql.getLoginDto()");

            List<LoginDto> loginDtos = new List<LoginDto>();

            DbConnection con = null;
            DbCommand pstmt = null;
            DbDataReader rs = null;

            LoginDto logIn = null;

            try
            {
                con = getDatabaseConnection();
                pstmt = CreateDBCommand(con, SQL_SELECT_LOGIN);
                rs = pstmt.ExecuteReader();

                while (rs.Read())
                {
                    logIn = rsToDto(rs);
                    loginDtos.Add(logIn);
                }
            }
            catch (Exception e)
            {
                logger.fatal("Exception occured in LoginDaoSql.getLoginDto():" + e.Message, e);
                throw new Exception("Exception : LoginDaoSql.getLoginDto() : " + e.Message, e);
            }
            finally
            {
                if (pstmt != null) pstmt.Cancel();
                if (con != null) con.Close();
            }
            logger.debug("Exiting LoginDaoSql.getLoginDto()");
            return loginDtos;
        }
    }
}
